//引入express第三方模块
const express = require("express");
//创建路由器对象
const r = express.Router();
//引入连接数据库,不是中间件不能写在app.js中
const pool = require('../mysql.js');
const fs = require('fs');
const path = require('path')

//试卷，出卷
r.post('/sj',(req,res,next)=>{
	// console.log(req.body.sj)
	
	pool.query('insert into exam (e_sj,eid) values (?,?)',[req.body.sj,req.body.eid],(err,result)=>{
		if (err){
			  next(err);//抛给下一个中间件来处理
			  //阻止往后执行
			  return;
		  }
		console.log('插入1')
		res.send({
			status: 200,
			msg: '插入成功'
		})
	})
	pool.releaseConnection(pool);
	
	
})

//试卷查询
r.get('/examsearch',(req,res,next)=>{
	pool.query('select * from exam',(err,result)=>{
		if(err){
			next(err);
			return;
		}
		// console.log(result);
		if(result.length==0){
			console.log('查询失败');
			res.send({status:201,msg:'查询失败'})
		}else{
			res.send({status:200,msg:'查询成功',data:result})
		}
	})
	pool.releaseConnection(pool);
})

//查询班级表
r.get('/selectclassroom',(req,res,next)=>{
	
	pool.query('select classroom from classroom',(err,result)=>{
		if (err) {
			next(err);
			return;
		}
		
		if (result.length == 0) {
			console.log('没有班级信息');
			res.send({
				status: 201,
				msg: '查询失败'
			})
		} else {
			res.send({
				status: 200,
				msg: '查询成功',
				data: result
			})
		}
	})
	pool.releaseConnection(pool);
})

//删除班级
r.delete('/deleteclassroom/:classroom',(req,res,next)=>{
	pool.query('delete from classroom where classroom=?',[req.params.classroom],(err,result)=>{
		if(err){
			next(err);
			return;
		}
		if (result.affectedRows == 0) {
			res.send({
				status: 201,
				msg: '删除失败'
			})
		} else {
			res.send({
				status: 200,
				msg: '删除成功'
			})
		}
	})
	pool.releaseConnection(pool);
})


//发布考试信息
r.post('/examinfo',(req,res,next)=>{
	
	pool.query('insert into examinfo (e_name,classroom,eid,start_time,end_time) values (?,?,?,?,?)',[req.body.testname,req.body.classroom,req.body.selecteid,req.body.starttime,req.body.endtime],(err,result)=>{
		if (err){
			  next(err);//抛给下一个中间件来处理
			  //阻止往后执行
			  return;
		  }
		console.log('发布考试信息成功')
		res.send({
			status: 200,
			msg: '插入成功'
		})
	})
	pool.releaseConnection(pool);
	
	
})

//查询发布考试信息
r.get('/examinfo',(req,res,next)=>{
	pool.query('select * from examinfo',(err,result)=>{
		if(err){
			next(err);
			return;
		}
		// console.log(result);
		if(result.length==0){
			console.log('查询失败');
			res.send({status:201,msg:'查询失败'})
		}else{
			res.send({status:200,msg:'查询成功',data:result})
		}
	})
	pool.releaseConnection(pool);
})


//删除已发布的考试信息
r.delete('/deletetest/:testname',(req,res,next)=>{
	pool.query('delete from examinfo where e_name=?',[req.params.testname],(err,result)=>{
		if(err){
			next(err);
			return;
		}
		if (result.affectedRows == 0) {
			res.send({
				status: 201,
				msg: '删除考试信息失败'
			})
		} else {
			res.send({
				status: 200,
				msg: '删除成功'
			})
		}
	})
	pool.releaseConnection(pool);
})



//修改考试时间
r.put('/examtime',(req,res,next)=>{
	pool.query('update examinfo set start_time=?,end_time=? where e_name=?',[req.body.starttime,req.body.endtime,req.body.ename],(err,result)=>{
		if(err){
			next(err);
			return;
		}
		if (result.affectedRows == 0) {
			res.send({
				status: 201,
				msg: '修改失败'
			})
		} else {
			res.send({
				status: 200,
				msg: '修改成功'
			})
		}
		
	})
	pool.releaseConnection(pool);
})

//添加班级
r.post('/addclassroom',(req,res,next)=>{
	pool.query('insert into classroom (classroom) values(?)',[req.body.classroom],(err,result)=>{
		if (err){
			  next(err);//抛给下一个中间件来处理
			  //阻止往后执行
			  return;
		  }
		console.log('添加班级成功')
		res.send({
			status: 200,
			msg: '插入成功'
		})
	})
	pool.releaseConnection(pool);
})

//查询所有学生信息
r.get('/searchstudentinfo',(req,res,next)=>{
	pool.query('select u_name,phone,sex,birthday,email,classroom,name,studentid from studentinfomation',(err,result)=>{
		if(err){
			next(err);
			return;
		}
		// console.log(result);
		if(result.length==0){
			console.log('查询失败');
			res.send({status:201,msg:'查询学生信息失败'})
		}else{
			res.send({status:200,msg:'查询成功',data:result})
		}
	})
	pool.releaseConnection(pool);
})

//按姓名查找学生信息
//select u_name,phone,sex,birthday,email,classroom,name,studentid from studentinfomation where name=?
r.get('/bynamesearchstudentinfo/:name',(req,res,next)=>{
	//模糊查询 select * from studentinfomation where name like '%肖%'
	pool.query('select u_name,phone,sex,birthday,email,classroom,name,studentid from studentinfomation where name like ?',[`%${req.params.name}%`],(err,result)=>{
		if(err){
			next(err);
			return;
		}
		// console.log(result);
		if(result.length==0){
			console.log('通过姓名查询学生信息失败');
			res.send({status:201,msg:'通过姓名查询学生信息失败'})
		}else{
			res.send({status:200,msg:'查询成功',data:result})
		}
	})
	pool.releaseConnection(pool);
})

//删除学生信息
r.delete('/deletestudentinfo/:uname',(req,res,next)=>{
	pool.query('delete from studentinfomation where u_name=?',[req.params.uname],(err,result)=>{
		if(err){
			next(err);
			return;
		}
		if (result.affectedRows == 0) {
			res.send({
				status: 201,
				msg: '删除学生信息失败'
			})
		} else {
			res.send({
				status: 200,
				msg: '删除成功'
			})
		}
	})
	pool.releaseConnection(pool);
})

//查找学生考生信息
r.get('/searchstudentexaminfo',(req,res,next)=>{
	pool.query('select * from studentexaminfo',(err,result)=>{
		if(err){
			next(err);
			return;
		}
		// console.log(result);
		if(result.length==0){
			console.log('查询学生考试信息失败');
			res.send({status:201,msg:'查询学生考试信息失败'})
		}else{
			res.send({status:200,msg:'查询成功',data:result})
		}
	})
	pool.releaseConnection(pool);
})

//删除学生考试信息
r.delete('/deletestudentexaminfo/:name&:e_name',(req,res,next)=>{
	pool.query('delete from studentexaminfo where name=? and e_name=?',[req.params.name,req.params.e_name],(err,result)=>{
		if(err){
			next(err);
			return;
		}
		if (result.affectedRows == 0) {
			res.send({
				status: 201,
				msg: '删除学生考试信息失败'
			})
		} else {
			res.send({
				status: 200,
				msg: '删除成功'
			})
		}
	})
	pool.releaseConnection(pool);
})

//通过姓名查找学生信息
//select * from studentexaminfo where name=?
r.get('/bynamesearchstudentexaminfo/:name',(req,res,next)=>{
	//模糊查询 select * from studentinfomation where name like '%肖%'
	pool.query('select * from studentexaminfo where name like ?',[`%${req.params.name}%`],(err,result)=>{
		if(err){
			next(err);
			return;
		}
		// console.log(result);
		if(result.length==0){
			console.log('通过姓名查询学生考试信息失败');
			res.send({status:201,msg:'通过姓名查询学生信息失败'})
		}else{
			res.send({status:200,msg:'查询成功',data:result})
		}
	})
})

//导出路由器对象
module.exports = r;